﻿using NB.MGJ.FrameWork;
using NB.MGJ.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.International.Converters.PinYinConverter;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data;
using NB.MGJ.Model.Model;

namespace NB.MGJ.Service
{
    public class BasicOriginModuleService
    {
        private BaseRepository _baseRepository = new BaseRepository();


        /// <summary>
        /// 获取用户所建所有模块
        /// </summary>
        public IEnumerable<Origins_BasicModule> GetBasicModule(int userId, int enterpriseId)
        {
            return _baseRepository.ExecuteTextCollection<Origins_BasicModule>(string.Format("SELECT * FROM origins_basicmodule WHERE CreateUserId = {0} AND CreateEnterpriseId = {1}", userId, enterpriseId));
        }


        /// <summary>
        /// 添加表记录-----主库
        /// </summary>
        public void AddOriginsBasicModule(Origins_BasicModule model)
        {
            model.TablesName = GetTableName(model.TableChineseName);
            try
            {
                var _table_ = new BaseRepository().ExecuteTextCollection<string>(string.Format(SqlString.TableIsExists, model.TablesName)).FirstOrDefault();
                if (!string.IsNullOrWhiteSpace(_table_))
                {
                    throw new Exception("表已存在");
                }
                _baseRepository.ExecuteTextCollection<object>(string.Format(SqlString.CreateString, model.TablesName, string.Empty));
                _baseRepository.Insert<Origins_BasicModule>(model);
            }
            catch
            {
                throw new Exception("当前无法执行该操作");
            }
        }


        //删除主表记录与从表
        public void DeleteOriginBasicModule(int id, int userId, int enterpriseId, string tableName)
        {
            try
            {
                _baseRepository.ExecuteText<int>(string.Format("DELETE  FROM  origins_basicmodule  WHERE  ID ={0} AND  CreateUserId ={1} AND CreateEnterpriseId = {2}", id, userId, enterpriseId));
                //分开自有深意
                _baseRepository.ExecuteText<int>(string.Format(SqlString.DropTable, tableName));
            }
            catch (Exception)
            {

                throw new Exception("当前无法执行该操作");
            }

        }

        //修改主库与从表名
        public void ModifyTableName(string tableName,string oldtableName, int Id)
        {
            if (!string.IsNullOrWhiteSpace(tableName))
            {
                var _table_english = GetTableName(tableName);

                if (!string.IsNullOrWhiteSpace(_table_english))
                {
                    //using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString))
                    //{
                    //    using (MySqlCommand cmd = conn.CreateCommand())
                    //    {
                    //        conn.Open();
                    //        cmd.CommandText = string.Format("SELECT TablesName FROM origins_basicmodule WHERE ButtonId ={0}", ButtonId);
                    //        cmd.CommandType = CommandType.Text;
                    //        string _oldName = cmd.ExecuteScalar().ToString();
                    //        if (!string.IsNullOrWhiteSpace(_oldName))
                    //        {
                    //            if (_oldName == _table_english)
                    //            {

                    //            }
                    //        }
                    //    }
                    //    conn.Dispose();
                    //    conn.Close();
                    //}
                    _baseRepository.ExecuteText<int>(string.Format("UPDATE  origins_basicmodule SET  TablesName = '{0}',TableChineseName='{1}' WHERE ID = {2} ", _table_english, tableName, Id));
                    //不要问为什么分开
                    _baseRepository.ExecuteText<int>(string.Format(SqlString.ModifyTableName, oldtableName, _table_english));
                }

            }
        }


        /// <summary>
        /// 获取表结构
        /// </summary>
        /// <returns></returns>
        public IEnumerable<TableColumns> GetTablesStructure(string tableName)
        {
            return _baseRepository.ExecuteTextCollection<TableColumns>(string.Format(SqlString.GetTableStructure, tableName));
        }


        public List<TableColumns> AddTableColumns(string tableName, List<TableColumns> columns)
        {
            string _feild = string.Empty;
            using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString))
            {
                try
                {
                    using (MySqlCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd.CommandText = string.Format(SqlString.GetTableStructure, tableName);
                        cmd.CommandType = CommandType.Text;
                        var _reader = cmd.ExecuteReader();
                        while (_reader.Read())
                        {
                            _feild += _reader.GetString("Field") + ",";
                        }
                        var _new_col = (from m in columns.Select(p => { p.Field = GetTableName(p.Field, false); return p; }) where !_feild.Contains(m.Field) select string.Format("ADD `{0}`  VARCHAR(100)  DEFAULT  '' ", m.Field)).ToArray();
                        var _new_feild = string.Join(",", _new_col);
                        _reader.Close();
                        cmd.CommandText = string.Format(SqlString.AddColumn, tableName, _new_feild);
                        cmd.CommandText += ";"+string.Format(SqlString.GetTableStructure, tableName);
                        _reader = cmd.ExecuteReader();
                        List<TableColumns> _tableColumns = new List<TableColumns>();
                        while (_reader.Read())
                        {
                            _tableColumns.Add(new TableColumns
                            {
                                Field = _reader.GetString("Field"),
                                Type = _reader.GetString("Type")
                            });
                        }
                        return _tableColumns;
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
        }


        public IEnumerable<TableColumns> DeleteTableColumn(string tableName, string feildName)
        {
            IEnumerable<TableColumns> _col = new List<TableColumns>();
            try
            {
                _col =  _baseRepository.ExecuteSqlCollection<TableColumns>(string.Format(SqlString.DropColumn, tableName, feildName) + ";" + string.Format(SqlString.GetTableStructure, tableName));
            }
            catch{}
            return _col;
        }


        public DataTable GetTableRowsData(string tableName,string where)
        {
            var _search = string.Format("SELECT * FROM {0} ", tableName);
            if (!string.IsNullOrWhiteSpace(where))
            {
                _search += "   WHERE  " + where;
            }
            return _baseRepository.GetDataTable(_search);
            //return _baseRepository.ExecuteSqlCollection<DataTable>(" SELECT  *  FROM  " + tableName);
        }

        /// <summary>
        /// 更新行数据
        /// </summary>
        public void UpdateTableRowsData(string[] dataRow, string tableName, string Id)
        {
            var tableCol = GetTablesStructure(tableName);
            var _count = tableCol.Count();
            tableCol = tableCol.Skip(1).Take(_count - 1);
            if (!string.IsNullOrWhiteSpace(Id))
            {
                string _set = string.Empty;
                if (tableCol != null)
                {
                    if (_count > 1)
                    {
                        int i = 0;
                        foreach (var item in tableCol)
                        {
                            _set += string.Format("`{0}` = '{1}',", item.Field, dataRow[i]);
                            i++;
                        }
                    }
                }
                _set = _set.Remove(_set.Length - 1);
                var _sql = string.Format(" UPDATE `{0}`  SET {1} WHERE ID = {2}", tableName, _set, Id);
                _baseRepository.ExecuteSql<int>(_sql);
            }
            else
            {
                InsertTableRowsData(dataRow, tableName, tableCol);
            }
        }


        /// <summary>
        /// 插入新数据
        /// </summary>
        public void InsertTableRowsData(string[] dataRow,string tableName,IEnumerable<TableColumns> tableCol)
        {
            string _col = string.Empty, _val = string.Empty;
            if (tableCol.Count()>0)
            {
                foreach (var item in tableCol)
                {
                    _col += string.Format("`{0}`,", item.Field);
                }
                for (int i = 0; i < dataRow.Length; i++)
                {
                    _val += string.Format("'{0}',", dataRow[i]);
                }
                _col = _col.Remove(_col.Length - 1);
                _val = _val.Remove(_val.Length - 1);

                var _sql = string.Format("INSERT INTO `{0}` ({1}) VALUES ({2})", tableName, _col, _val);
                var _id = _baseRepository.ExecuteSqlReturnID<int>(_sql);
            }
        }


        /// <summary>
        /// 删除表中数据
        /// </summary>
        public void DeleteTableRowsData(string Id,string tableName)
        {
            _baseRepository.ExecuteSql<int>(string.Format("DELETE FROM  `{0}`  WHERE ID = {1}", tableName, Id));
        }

        public static string GetTableName(string chineseName, bool needT = true)
        {

            string _result = string.Empty;
            if (string.IsNullOrWhiteSpace(chineseName))
            {
                return string.Empty;
            }
            for (int i = 0; i < chineseName.Length; i++)
            {
                if (ChineseChar.IsValidChar(chineseName[i]))
                {
                    var _r = new ChineseChar(chineseName[i]);
                    if (_r != null)
                    {
                        var _f = _r.Pinyins.FirstOrDefault();
                        if (ChineseChar.IsValidPinyin(_f))
                        {
                            _result += _f.Remove(_f.Length - 1);
                        }
                    }
                }
                else
                {
                    _result += chineseName[i];
                }

            }
            if (needT)
            {
                return "t_" + _result.ToLower();
            }
            return _result.ToLower();

        }
      
        public List<string> GetDBTypeList()
        {
            return new List<string> { 
            "TINYINT",
            "SMALLINT",
            "MEDIUMINT",
            "INT",
            "INTEGER",
            "BIGINT",
            "BIT",
            "DOUBLE",
            "FLOAT",
            "DECIMAL",
            "CHAR",
            "VARCHAR",
            "DATE",
            "TIME",
            "YEAR",
            "TIMESTAMP",
            "DATETIME",
            "TEXT"
            };
        }
    }

    public static class SqlString
    {
        public static string TableIsExists
        {
            get
            {
                return "SHOW TABLES LIKE '{0}'";
            }
        }
        //建表
        public static string CreateString
        {
            get
            {
                return @"CREATE TABLE {0}(
                       ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT
                       {1}
                       )";
            }
        }

        //添加字段
        public static string AddColumn
        {
            get
            {
                return @"ALTER TABLE `{0}` {1} ";
            }
        }

        //删除字段
        public static string DropColumn
        {
            get
            {
                return @"ALTER TABLE  `{0}`  DROP `{1}` ";
            }
        }

        //修改表名
        public static string ModifyTableName
        {
            get
            {
                return @"ALTER TABLE {0} RENAME {1}";
            }
        }

         //删除表
        public static string DropTable
        {
            get
            {
                return @"DROP TABLE IF EXISTS {0}";
            }
        }

        //获取表结构
        public static string GetTableStructure
        {
            get
            {
                return @"SHOW COLUMNS FROM {0}";
            }
        }
    }
}
